from flask import Blueprint, render_template, redirect, request, url_for
from utils import restful
from config import db, cursor
import jpype
from jpype import *

bp = Blueprint('cms', __name__, url_prefix='/cms')

@bp.route('/')
def index():
    # 关于学生的信息，，都是有关于作图的
    male_num = "select count(Sno) from S where Sgender='男' and isnull=0"
    female_num = "select count(Sno) from S where Sgender='女' and isnull=0"
    cursor.execute(male_num)
    male_num = cursor.fetchone()[0]

    cursor.execute(female_num)
    female_num = cursor.fetchone()[0]
    # 统计学生出生年份
    SbirthdayCount = """
      SELECT DATE_FORMAT(Sbirthday,"%Y"), COUNT(*) from S group by DATE_FORMAT(Sbirthday,"%Y");
    """
    cursor.execute(SbirthdayCount)
    SbirthdayCount = cursor.fetchall()  # (('1997', 2), ('1998', 3), ('1999', 1))
    # 把统计的结果分别存进两个列表中
    SbirthCountYearList = list(map(lambda x: x[0], SbirthdayCount))  # ['1997','1998','1999']
    SbirthCountNumList = list(map(lambda x: x[1], SbirthdayCount))  # [2,3,1]

    # 有关于教师的信息，关于作图
    TmaleNum = "select count(Tno) from T where Tgender='男'"
    TfemaleNum = "select count(Tno) from T where Tgender='女'"
    cursor.execute(TmaleNum)
    TmaleNum = cursor.fetchone()[0]
    cursor.execute(TfemaleNum)
    TfemaleNum = cursor.fetchone()[0]
    # 统计教师出生年份
    TbirthdayCount = """
          SELECT DATE_FORMAT(Tbirthday,"%Y"), COUNT(*) from T group by DATE_FORMAT(Tbirthday,"%Y");
        """
    cursor.execute(TbirthdayCount)
    TbirthdayCount = cursor.fetchall()  # (('1997', 2), ('1998', 3), ('1999', 1))
    # 把统计的结果分别存进两个列表中
    TbirthCountYearList = list(map(lambda x: x[0], TbirthdayCount))  # ['1997','1998','1999']
    TbirthCountNumList = list(map(lambda x: x[1], TbirthdayCount))  # [2,3,1]

    # 统计每个部门有多少教师
    TdepartCount = "SELECT COUNT(TNO), depart from T group by depart"
    cursor.execute(TdepartCount)
    TdepartCount = cursor.fetchall()  # ((1, '微电子'), (2, '物联网'), (3, '计算机'))
    TdepartNumList = list(map(lambda x: x[0], TdepartCount))  # [1,2,3]
    TdepartNameList = list(map(lambda x: x[1], TdepartCount))  # ['微电子'，'物联网','计算机']

    # 统计教师的职称分布
    TprofCount = "SELECT COUNT(TNO), prof from T group by prof"
    cursor.execute(TprofCount)
    TprofCount = cursor.fetchall()
    TprofNumList = list(map(lambda x: x[0], TprofCount))
    TprofNameList = list(map(lambda x: x[1], TprofCount))
    dict = {
        'Smale_num': male_num,
        'Sfemale_num': female_num,
        'TmaleNum': TmaleNum,
        'TfemaleNum': TfemaleNum,
        'SbirthCountYearList': SbirthCountYearList,
        'SbirthCountNumList': SbirthCountNumList,
        'TbirthCountYearList': TbirthCountYearList,
        'TbirthCountNumList': TbirthCountNumList,
        'TdepartNumList': TdepartNumList,
        'TdepartNameList': TdepartNameList,
        'TprofNumList': TprofNumList,
        'TprofNameList': TprofNameList
    }
    return render_template('cms/index.html', **dict)


@bp.route('/tab_panel/', methods=['GET', 'POST'])
def tab_panel():
    results = ''   # 对手动输入的sql查询进行初始化处理
    inputTypeFlag = True  # 对输入的sql语句是否为查询类型进行判定
    sql11 = "select AVG(SC.grade) from SC, C where C.Cname like '计%' and C.Cno=SC.CNO group by C.Cno having COUNT(C.Cno)>4"  # 能查询至少有5名学生选修的并以'计'开头的课程的平均分数。

    sql14 = "select S.SNO,S.Sname,SC.grade from S,SC where SC.Cno='3' and S.Sno=SC.Sno and SC.grade>(select grade from SC where Cno='3' and Sno='5')"  # 查询选修“3”课程的成绩高于“5”号同学成绩的所有同学的记录。

    sql15 = "select Sno,Sname,Sbirthday from S where year(S.Sbirthday)=(select year(Sbirthday) from S where Sno='1')"  # 查询和学号为1的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

    sql18 = "select distinct cno from SC where grade>85"  # 能查询存在有某个分数（比如85分）以上成绩的课程号.

    sql20 = "select * from SC where Cno='3' and grade >any(select grade from SC where Cno='4') order by grade DESC"  # 能查询3号的课程且成绩至少高于4号课程的同学的学号、课程号以及成绩，并按成绩从高到低次序排序

    sql22 = "select Sno,Cno,grade from SC a where a.grade< (select AVG(grade) from SC b where a.Cno=b.Cno)"  #  能查询成绩比该课程平均成绩低的同学的成绩表。

    sql26 = "select MAX(Sbirthday) as 最小,MIN(Sbirthday) as 最大 from S"  # 查询学生最大和最小的出生日期值。

    sql28 = "select Sname from S where Sgender=(select Sgender from S where Sname='张三') and Sname not in ('张三') and SClass=(select SClass from S where Sname='张三')"

    cursor.execute(sql11)
    sql11 = cursor.fetchall()
    cursor.execute(sql14)
    sql14 = cursor.fetchall()
    cursor.execute(sql15)
    sql15 = cursor.fetchall()
    cursor.execute(sql18)
    sql18 = cursor.fetchall()
    cursor.execute(sql20)
    sql20 = cursor.fetchall()
    cursor.execute(sql22)
    sql22 = cursor.fetchall()
    cursor.execute(sql26)
    sql26 = cursor.fetchall()
    cursor.execute(sql28)
    sql28 = cursor.fetchall()
    if request.method == 'POST':
        inputSql = request.values.get("inputSql")
        inputSql = inputSql.replace(r"，", r",").upper()   # 自动将中文逗号 转换成英文逗号
        print(inputSql)
        if not inputSql.startswith('SELECT'):
           inputTypeFlag = False
        try:
            cursor.execute(inputSql)
            results = cursor.fetchall()
            print(results)
        except:
            print("fail")
    dict = {
        'sql11': sql11,
        'sql14': sql14,
        'sql15': sql15,
        'sql18': sql18,
        'sql20': sql20,
        'sql22': sql22,
        'sql26': sql26,
        'sql28': sql28,
        'results': results,
        'inputTypeFlag': inputTypeFlag
    }
    return render_template('cms/tab-panel.html', **dict)


@bp.route('/student/', methods=['GET', 'POST'])
def student():
    resultHasGrade = False   # 查询结果是否含有成绩的标志

    # 查询出课程名
    course = "select Cname from C"
    cursor.execute(course)
    courses = cursor.fetchall()  # (('高等数学',), ('线性代数',), ('数据库原理',)
    #     需要把courses转换成字符串 "高等数学，线性代数，数据库原理"
    courses = list(map(lambda x: x[0], courses))
    courses = "'{}'".format(",".join(courses))

    # 查询出班级
    Sclass = "select distinct Sclass  from S"
    cursor.execute(Sclass)
    Sclass = cursor.fetchall()
    # 同理，班级也要转换格式
    Sclass = list(map(lambda x: x[0], Sclass))
    Sclass = "'{}'".format(",".join(Sclass))

    if request.method == 'GET':
        student = "select * from S where isnull=0"
        cursor.execute(student)
        students = cursor.fetchall()

    else:
        sql = "select * from S where isnull=0"

        gender = request.values.get('Sgender')
        if gender != '男' and gender != '女':  # 如果未选择学生性别，则把性别设为空
            sql = sql + " and 1=1"
        else:
            sql = sql + " and Sgender='{}'".format(gender)
        option = request.values.get('provinces')  # 得到选择查询的类别

        if option == '0':  # 按照学号查询
            SnoInput = request.values.get('textInput')
            sql = sql + " and SNO='{}'".format(SnoInput)
        elif option == '1':  # 按照班级查询
            sclass = request.values.get('city')
            sql = sql + " and Sclass='{}'".format(sclass)

        elif option == '2':  # 按照成绩查询
            gradeMin = request.values.get('textInput')  # 分数下线
            gradeMax = request.values.get('low')   # 分数上线
            subject = request.values.get('city')  # 学科
            CNO_sql = "select CNO from C where Cname='{}'".format(subject)
            print(CNO_sql)
            cursor.execute(CNO_sql)
            CNO = cursor.fetchone()[0]
            sql = """
                select S.SNO, S.Sname, S.Sgender, S.Sclass, C.Cname, SC.grade 
                from S, C, SC
                where S.isnull=0 and S.SNO=SC.SNO and C.CNO=SC.CNO and C.CNO='{}' and SC.grade>={} and SC.grade<={} 
            """.format(CNO, gradeMin, gradeMax)
            resultHasGrade = True

        elif option == '3':  # 按照姓名查询
            Sname = request.values.get('textInput')
            sql = sql + " and Sname='{}'".format(Sname)

        elif option == '4':  # 按照选修课查询
            subject = request.values.get('city')
            CNO_sql = "select CNO from C where Cname='{}'".format(subject)
            cursor.execute(CNO_sql)
            CNO = cursor.fetchone()[0]
            sql = """
                select S.SNO, S.Sname, S.Sgender, S.Sclass, C.Cname, SC.grade 
                from S, C, SC
                where S.isnull=0 and S.SNO=SC.SNO and C.CNO=SC.CNO and C.CNO='{}'
            """.format(CNO)
            resultHasGrade = True

        cursor.execute(sql)
        students = cursor.fetchall()

    dict = {
        'results': students,
        'courses': courses,
        'Sclass': Sclass,
        'flag': resultHasGrade
    }
    return render_template('cms/student/student.html', **dict)


@bp.route('/teacher/', methods=['GET', 'POST'])
def teacher():
    # 查询出老师的专业/系
    Tdepts = "select distinct depart from T"
    cursor.execute(Tdepts)
    Tdepts = cursor.fetchall()  # (('物联网',), ('计算机',), ('微电子',))
    Tdepts = list(map(lambda x: x[0], Tdepts))
    Tdepts = "'{}'".format(",".join(Tdepts))

    # 查询出课程名
    course = "select Cname from C"
    cursor.execute(course)
    courses = cursor.fetchall()  # (('高等数学',), ('线性代数',), ('数据库原理',)
    #     需要把courses转换成字符串 "高等数学，线性代数，数据库原理"
    courses = list(map(lambda x: x[0], courses))
    courses = "'{}'".format(",".join(courses))

    studentGradeSql = ''  # 查询学生成绩的sql
    studentGrade = ()
    courseGradeChart = []  # 成绩 作图使用
    SnameChart = []  # 学生姓名作图使用
    courseNameChart = ""  # 课程名称 作图使用

    TnameFlag = False   # 是否显示教师姓名

    if request.method == 'GET':
        sql = "select * from T"
        cursor.execute(sql)
        results = cursor.fetchall()

    else:
        sql = "select * from T"
        Tgender = request.values.get("Tgender")
        if Tgender != '男' and Tgender != '女':
            sql = sql + " where 1=1"
        else:
            sql = sql + " where Tgender='{}'".format(Tgender)

        option = request.values.get("provinces2")  # 获得选取的查询方式
        if option == '1':  # 按照姓名查找
            Tname = request.values.get("inputLow")
            sql = sql + " and Tname='{}'".format(Tname)
        elif option == '2':  # 按照工号查找
            TNO = request.values.get("inputLow")
            radio = request.values.get("radio")
            if radio == '1':  # 选择 查询学生成绩
                studentGradeSql = """
                    select S.SNO, S.Sname, C.Cname, SC.grade
                    from S, C, SC, TC
                    where S.isnull=0 and S.SNO=SC.SNO and C.CNO=SC.CNO and TC.CNO=SC.CNO and TC.TNO='{}'
                    order by SC.grade desc
                """.format(TNO)

                cursor.execute(studentGradeSql)
                studentGrade = cursor.fetchall()
                try:
                    courseGradeChart = list(map(lambda x: int(x[3]), studentGrade))  # 把学生成绩放进作图使用的成绩列表里
                    SnameChart = list(map(lambda x: x[1], studentGrade))  # 把学生姓名放进作图使用的姓名列表里

                    courseNameChart = "'{}'".format(studentGrade[0][2])  # 作图使用的课程名称
                except:
                    courseGradeChart = []
                    SnameChart = []
            sql = sql + " and TNO='{}'".format(TNO)
        elif option == '3':  # 按照课程查找
            subject = request.values.get("city2")
            radio = request.values.get("radio")
            if radio == '1':
                studentGradeSql = """
                                    select S.SNO, S.Sname, C.Cname, SC.grade,T.Tname
                                    from S, C, SC, TC, T
                                    where S.isnull=0 and S.SNO=SC.SNO and C.CNO=SC.CNO and TC.CNO=SC.CNO and T.TNO=TC.TNO and C.Cname='{}'
                                    order by SC.grade desc
                                """.format(subject)
                TnameFlag = True  # 显示出教师的姓名
                try:
                    cursor.execute(studentGradeSql)
                    studentGrade = cursor.fetchall()

                    courseGradeChart = list(map(lambda x: int(x[3]), studentGrade))  # 把学生成绩放进作图使用的成绩列表里
                    SnameChart = list(map(lambda x: x[1], studentGrade))  # 把学生姓名放进作图使用的姓名列表里
                    courseNameChart = "'{}'".format(studentGrade[0][2])  # 作图使用的课程名称
                except:
                    courseGradeChart = []
                    SnameChart = []

            if Tgender == '男' or Tgender == '女':
                sql = """
                    select * from T where TNO in(
                        select TNO from TC where CNO in(
                          select CNO from C where Cname='{}'
                        )) and Tgender='{}'
                """.format(subject, Tgender)
            else:
                sql = """
                    select * from T where TNO in(
                        select TNO from TC where CNO in(
                          select CNO from C where Cname='{}'
                        ))
                """.format(subject)
        elif option == '4':  # 按照部门查找
            dept = request.values.get("city2")
            sql = sql + " and depart='{}'".format(dept)

        elif option == '6':  # 按照是否授课查找
            dept = request.values.get("city2")
            radio = request.values.get("radio")
            sql = sql + " and depart='{}' and stat='{}'".format(dept, radio)
        print(sql)
        cursor.execute(sql)
        results = cursor.fetchall()
    dict = {
        'results': results,  #  教师信息查询结果（必有）
        'Tdepts': Tdepts,  # 教师专业/系/部门（必有）
        'courses': courses,   # 所有课程名称 （必有）
        'studentGrade': studentGrade,   # 学生成绩（可能为空）
        'TnameFlag': TnameFlag,   # 是否显示教师姓名
        'courseGradeChart': courseGradeChart,  # 作图使用的成绩列表
        'SnameChart': SnameChart,  # 作图使用的姓名列表
        'courseNameChart': courseNameChart  # 作图使用的课程名称
    }
    return render_template('cms/teacher/teacher.html', **dict)

@bp.route('/addstudent/', methods=['GET', 'POST'])
def addStudent():
    # 查询出班级
    Sclass = "select distinct Sclass  from S"
    cursor.execute(Sclass)
    Sclass = cursor.fetchall()
    # 班级要转换格式
    Sclass = list(map(lambda x: x[0], Sclass))
    if request.method == 'GET':
        dict = {
            'Sclass': Sclass
        }
        return render_template('cms/student/addStudent.html', **dict)
    else:
        SNO = request.values.get("SnoInput")
        Sname = request.values.get("NameInput")
        Sgender = request.values.get("Sgender")
        sclass = request.values.get("Sclass")
        Sbirth = request.values.get("SbirthInput")
        sql = "insert into S VALUES ('{}','{}', '{}','{}', '{}','0')".format(SNO, Sname, Sgender, sclass, Sbirth)
        try:
            cursor.execute(sql)
            db.commit()
            print("success")
        except:
            print("fail")
            db.rollback()
        return redirect(url_for('cms.student'))


@bp.route('/addteacher/', methods=['GET', 'POST'])
def addTeacher():
    # 查询出班级
    depart = "select distinct depart  from T"
    cursor.execute(depart)
    depart = cursor.fetchall()
    # 班级要转换格式
    depart = list(map(lambda x: x[0], depart))
    if request.method == 'GET':
        dict = {
            'departs': depart
        }
        return render_template('cms/teacher/addTeacher.html', **dict)
    else:
        TNO = request.values.get("TnoInput")
        Tname = request.values.get("NameInput")
        Tgender = request.values.get("Tgender")
        prof = request.values.get("prof")
        stat = request.values.get("stat")
        depart = request.values.get("depart")
        Tbirth = request.values.get("TbirthInput")
        sql = "insert into T VALUES ('{}','{}', '{}','{}', '{}','{}','{}','0')".format(TNO, Tname, Tgender, depart, prof, Tbirth, stat)
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()
        return redirect(url_for('cms.teacher'))


@bp.route('/editStudent/', methods=['GET', 'POST'])
def editStudent():
    Sno = request.values.get('Sno')
    print(Sno)
    if request.method == 'GET':
        sql = "select * from S where SNO='{}'".format(Sno)
        cursor.execute(sql)
        student = cursor.fetchone()  # ('1', '张三', '男', '物联1604', '1997/2/22')

        # 查询出班级
        Sclass = "select distinct Sclass  from S"
        cursor.execute(Sclass)
        Sclass = cursor.fetchall()
        # 班级要转换格式
        Sclass = list(map(lambda x: x[0], Sclass))
        return render_template('cms/student/editStudent.html', student=student, Sclass=Sclass)
    else:
        Sclass = request.values.get('Sclass')
        sql = "update S set Sclass='{}' where SNO='{}'".format(Sclass, Sno)
        try:
            cursor.execute(sql)
            db.commit()
            print("edit success")
        except:
            print("edit fail")
            db.rollback()
        return redirect(url_for('cms.student'))


@bp.route('/deleteStudent/', methods=['POST'])
def deleteStudent():
    Sno = request.form.get('SNO')
    sql = "update S set isnull='1' where SNO='{}'".format(Sno)
    try:
        cursor.execute(sql)
        db.commit()
        print("delete success")
        return restful.success()
    except:
        db.rollback()
        print("delete fail")
        return restful.params_error('删除失败')

@bp.route('/other/')
def other():
    jvmPath = jpype.get_default_jvm_path()
    jpype.startJVM(jvmPath, r"-Djava.class.path=/media/hp/0F1C075C0F1C075C/pycharm-project/database_homework/sqlObject.jar")
    JDClass = JClass("Temp.sqlobj")
    jd = JDClass()
    sql2 = jd.getSql2()
    sql15 = jd.getSql15('1')
    jpype.shutdownJVM()
    return sql2
